<?php

class tokenizer {

	private $querysections = array('select', 'from', 'where', 'limit', 'order', 'group');
	private $tree = array();
	private $order;
	private $limit;
	private $derivedTable;
	const DOT_IN_VARNAME = "_DOT_";

	public function __construct($sqlQuery) {
		$sqlQuery = ltrim(preg_replace('/[\\s]{2,}/', ' ', $sqlQuery));
		/**
		 * Regular expression based on SQL::Tokenizer's Tokenizer.pm by Igor Sutton Lopes
		 * */
		$regex = '('; # begin group
		$regex .= '(?:--|\\#)[\\ \\t\\S]*'; # inline comments
		$regex .= '|(?:<>|<=>|>=|<=|==|=|!=|!|<<|>>|<|>|\\|\\||\\||&&|&|-|\\+|\\*(?!\/)|\/(?!\\*)|\\%|~|\\^|\\?)'; # logical operators
		$regex .= '|[\\[\\]\\(\\),;`]|\\\'\\\'(?!\\\')|\\"\\"(?!\\"")'; # empty single/double quotes
		$regex .= '|".*?(?:(?:""){1,}"|(?<!["\\\\])"(?!")|\\\\"{2})|\'.*?(?:(?:\'\'){1,}\'|(?<![\'\\\\])\'(?!\')|\\\\\'{2})'; # quoted strings
		$regex .= '|\/\\*[\\ \\t\\n\\S]*?\\*\/'; # c style comments
		$regex .= '|(?:[\\w:@]+(?:\\.(?:\\w+|\\*)?)*)'; # words, placeholders, database.table.column strings
		$regex .= '|[\t\ ]+';
		$regex .= '|[\.]'; #period
		$regex .= ')'; # end group
		// Catching derived tables
		preg_match_all("/(\sFROM\s+(\(.*\)\s+as\s+\w*)?\s+)/smi", $sqlQuery, $m);
		if (isset($m[2][0])) {
			$this->derivedTable = $m[2][0];
			$sqlQuery = str_replace($m[2][0], '__DERIVED_TABLE__', $sqlQuery);
		}
		// Break unions
		$i = 0;
		foreach (preg_split("/\sUNION\s/smi", $sqlQuery) as $query) {
			// Get global match
			preg_match_all('/' . $regex . '/smx', $query, $result);
			$tokens = $result[0];
			$section = $tokens[0];
			// Parsing tokens
			for ($t = 0; $t < sizeof($tokens); $t++) {
				if (in_array($tokens[$t], array('{', '('))) {
					// Building tree
					$this->setToken($section, $this->readSub($tokens, $t), $i);
				} else {
					if (in_array(strtolower($tokens[$t]), $this->querysections) && !isset($this->tree[$i][$tokens[$t]])) {
						$section = strtolower($tokens[$t]);
					}
					// Building tree
					$this->setToken($section, $tokens[$t], $i);
				}
			}
			$i++;
		}
	}
	
	public static function safe($var){
		if(is_array($var)){
			foreach($var as $key=>$value){
				$var[$key] = self::safe($value);
			}
		}else{
			$var = trim(strip_tags($var));
		}
		return $var;
	}
	
	public function set_limit($sql, $limit, $offset) {
		if (false!==stripos($sql, 'LIMIT')) $sql = preg_replace('/\sLIMIT\s+[^a-z]+/i', ' ', $sql);
		if (false!==stripos($sql, 'OFFSET')) $sql = preg_replace('/\sOFFSET\s+\d+/i', '', $sql);
		return $sql." LIMIT ".$limit." OFFSET ".$offset;
	}	

	/**
	 * Parses a section of a query (usually a sub-query or where clause)
	 *
	 * @param	array		$tokens
	 * @param	integer		$position
	 * @return	string
	 */
	private function readSub($tokens, &$position) {
		$subs = 0;
		$sub = $tokens[$position];
		$tokenCount = count($tokens);
		$position++;
		while (!in_array($tokens[$position], array('}', ')')) && $position < $tokenCount) {

			if (in_array($tokens[$position], array('{', '('))) {
				$sub.= $this->readsub($tokens, $position);
				$subs++;
			} else {
				$sub.= $tokens[$position];
			}
			$position++;
		}
		$sub.= $tokens[$position];
		return $sub;
	}

	/**
	 * Returns a limit section of the query
	 *
	 * @return string
	 */	
	public function getLimit() {
		return $this->limit;
	}
	
	/**
	 * Returns a statement section of the query
	 *
	 * @param string $statement		the required SQL statement
	 * @return string
	 */
	public function getToken($statement, $iterator=false) {
		$statement = strtolower($statement);
		if (false === $iterator) {
			$result = ltrim(substr($this->{$statement}, stripos($this->{$statement}, $statement) + strlen($statement)));
			if ($this->startsWith($result, "by"))
				return ltrim(substr($result, 3));
		}else {
			if (!isset($this->tree[$iterator][$statement]))
				return false;
			$result = ltrim(substr($this->tree[$iterator][$statement], stripos($this->tree[$iterator][$statement], $statement) + strlen($statement)));
			if ($this->startsWith($result, "by"))
				return ltrim(substr($result, 3));
		}
		return $result;
	}
	
	private function setToken($statement, $value, $iterator = false) {
		if (false === $iterator || in_array($statement, array("limit", "order"))) {
			$this->{$statement}.= $value;
		} else {
			if (!isset($this->tree[$iterator][$statement]))
				$this->tree[$iterator][$statement] = $value;
			else
				$this->tree[$iterator][$statement].= $value;
		}
	}

	/**
	 * Test if the string starts with a required substring
	 *
	 * @param	string	$subject	the whole string
	 * @param	string	$needle		the required substring
	 * @param	boolean	$sensitive	true for case sensitive check
	 * @return	boolean
	 */
	public function startsWith($subject, $needle, $sensitive=false) {
		if ($sensitive)
			return 0 === strpos($subject, $needle);
		return 0 === stripos($subject, $needle);
	}

	/**
	 * Test if the string ends with a required substring
	 *
	 * @param	string	$subject	the whole string
	 * @param	string	$needle		the required substring
	 * @param	boolean	$sensitive	true for case sensitive check
	 * @return	boolean
	 */
	public function endsWith($stack, $needle, $sensitive=false) {
		if ($sensitive)
			return $needle == substr($stack, strripos($stack, $needle));
		return $needle == substr($stack, strrpos($stack, $needle));
	}

	/**
	 * Rebuilds the SQL query used in the Report
	 *
	 * @param	array	$filters		filters to use in the query
	 * @param	boolean	$forCount		true to not include the "SELECT fields" and ORDER sections
	 * @return	string
	 */
	public function rebuild(&$filters=array(), $forCount=false) {
		global $request;
		$sql = $union = "";

		foreach ($this->tree as $i => $array) {
			// SELECT
			$sql.=$union;
			$sql.= "SELECT " . $this->getToken("SELECT", $i);
			// FROM
			$sql.= " FROM " . $this->getToken("FROM", $i);
			// WHERE
			if ($this->getToken("WHERE", $i))
				$sql.= " WHERE (" . $this->getToken("WHERE", $i) . ")";
			else
				$sql.= " WHERE 1";
			foreach ($filters as $columnName => $properties) {
				$sqlColumnName = str_replace(self::DOT_IN_VARNAME, ".", $columnName);
				switch ($properties["type"]) {
					case "date":
						$filters[$columnName]["value"]["start"]=$filters[$columnName]["value"]["end"]="";
						if (isset($_POST[$columnName . "_start"]) && ("0" == $_POST[$columnName . "_start"] || !empty($_POST[$columnName . "_start"]))) {
							try {
								$filters[$columnName]["value"]["start"] = $startDate = date('Y-m-d', strtotime($_POST[$columnName . "_start"]));
								if ($properties["catch"]){
									$time = substr($_POST[$columnName . "_start"],11,8);
									if (!isset($time) || empty($time)){
										$time = '00:00:00';
									}
									$sql.= " AND $sqlColumnName >= '" . $startDate . " ".$time."'";
								}
							} catch (Exception $e) {
								
							}
						}
						if (isset($_POST[$columnName . "_end"]) && ("0" == $_POST[$columnName . "_end"] || !empty($_POST[$columnName . "_end"]))) {
							try {
								$filters[$columnName]["value"]["end"] = $endDate = date('Y-m-d', strtotime($_POST[$columnName . "_end"]));
								if ($properties["catch"]){
									$time = substr($_POST[$columnName . "_end"],11,8);
									if (!isset($time) || empty($time)){
										$time = '23:59:59';
									}
									$sql.= " AND $sqlColumnName <= '" . $endDate . " ".$time."'";
								}
							} catch (Exception $e) {
								
							}
						}
						break;
					case "checkbox":
					case "checkbox-inline":
					case "select-multiple":
						$filters[$columnName]["value"] = array();
						if (isset($_POST[$columnName]) && is_array($_POST[$columnName])) {
							$filters[$columnName]["value"] = self::safe($_POST[$columnName]);
							if ($properties["catch"])
								$sql.= " AND $sqlColumnName IN ('" . implode("','", $filters[$columnName]["value"]) . "')";
						}
						break;
					case "numeric":
						$filters[$columnName]["value"] = "";
						if (isset($_POST[$columnName]) && ("0" == $_POST[$columnName] || !empty($_POST[$columnName]))) {
							$filters[$columnName]["value"] = self::safe($_POST[$columnName]);
							if ($properties["catch"]){
								$pieces = explode(",", $filters[$columnName]["value"]);
								if (2 <= sizeof($pieces)) {
									foreach ($pieces as $key => $piece) {
										$pieces[$key] = trim($piece);
									}
									$sql.= " AND $sqlColumnName IN ('" . implode("','", $pieces) . "')";
								} else {
									$sql.= " AND $sqlColumnName = '" . $filters[$columnName]["value"] . "'";
								}
							}
							break;
						}
					case "numeric-range":
						$filters[$columnName]["value"]["min"]=$filters[$columnName]["value"]["max"]="";
						if (isset($_POST[$columnName . "_min"]) && ("0" == $_POST[$columnName . "_min"] || !empty($_POST[$columnName . "_min"]))){
							$filters[$columnName]["value"]["min"] = self::safe($_POST[$columnName . "_min"]);
							if ($properties["catch"])
								$sql.= " AND $sqlColumnName >= '" . $filters[$columnName]["value"]["min"] . "'";
						}
						if (isset($_POST[$columnName . "_max"]) && ("0" == $_POST[$columnName . "_max"] || !empty($_POST[$columnName . "_max"]))){
							$filters[$columnName]["value"]["max"] = self::safe($_POST[$columnName . "_max"]);
							if ($properties["catch"])
								$sql.= " AND $sqlColumnName <= '" . $filters[$columnName]["value"]["max"] . "'";
						}
						break;
					case "hidden":
					case "radio":
					case "radio-inline":
					case "select":
						$filters[$columnName]["value"] = "";
						if (isset($_POST[$columnName]) && ("0" == $_POST[$columnName] || !empty($_POST[$columnName]))) {
							$filters[$columnName]["value"] = self::safe($_POST[$columnName]);
							if ($properties["catch"])
								$sql.= " AND $sqlColumnName = '" . $filters[$columnName]["value"] . "'";
						}
						break;
					case "like_multiple":
						$filters[$columnName]["value"] = "";
						if (isset($_POST[$columnName]) && !empty ($_POST[$columnName])) {
							$filters[$columnName]["value"] = self::safe($_POST[$columnName]);
							if ($properties["catch"]){
								foreach(explode(",",$filters[$columnName]["value"]) as $value){
									$sql.= " AND ($sqlColumnName LIKE '%" . $value . "%')";
								}
							}
						}
						break;
					case "like_multiple_or":
						$filters[$columnName]["value"] = "";
						if (isset($_POST[$columnName]) && !empty ($_POST[$columnName])) {
							$filters[$columnName]["value"] = self::safe($_POST[$columnName]);
							if ($properties["catch"]){
								$sql.="AND (".PHP_EOL;
								$separator = "";
								foreach(explode(",",$filters[$columnName]["value"]) as $value){
									$sql.= $separator."($sqlColumnName LIKE '%" . $value . "%')".PHP_EOL;
									$separator=" OR ";
								}
								$sql.=")".PHP_EOL;
							}
						}
						break;
					default:
						$filters[$columnName]["value"] = "";
						if (isset($_POST[$columnName]) && ("0" == $_POST[$columnName] || !empty($_POST[$columnName]))) {
								$filters[$columnName]["value"] = self::safe($_POST[$columnName]);
								if ($properties["catch"])
									$sql.= " AND $sqlColumnName LIKE '%" . $filters[$columnName]["value"] . "%'";
							}
						}
				}
			// GROUP BY
			if ($this->getToken("GROUP", $i))
				$sql.= " GROUP BY " . $this->getToken("GROUP", $i);
			// adjust parameters
			$union = " UNION ";
		}
		// ORDER
		if (!$forCount) {
			if (isset($_POST['order']) && !empty($_POST['order'])) {
				$orders = explode(",", $_POST['order']);
				$directions = explode(",", $_POST['direction']);
				$separator = " ORDER BY ";
				foreach($orders as $key => $value){
					$sql.= $separator . self::safe($value) . " " . self::safe($directions[$key]);
					$separator = ",";
				}
			} else {
				if ($this->order) {
					$orders = explode(",",str_replace(array("ORDER BY ","`"),"",$this->order));
					$_POST['order'] = "";
					$_POST['direction'] = "";
					$separator = "";
					foreach($orders as $order){
						$fields = explode(" ", trim($order));
						$_POST["order"] .= $separator.$fields[0];
						$_POST["direction"] .= $separator.(isset($fields[1])?$fields[1]:"ASC");
						$separator = ",";
					}
					$sql.= " " . $this->order;
				}
			}
		}

		if ($this->limit != '') {
			$sql.= " " . $this->limit;
		}
		// Restoring derived table
		if ($this->derivedTable) {
			$sql = str_replace('__DERIVED_TABLE__', $this->derivedTable, $sql);
		}
		return $sql;
	}

} // End Tokenizer
